package net.sky.oaxz.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import net.sky.oaxz.bean.XzTeaComplex;
import net.sky.oaxz.bean.XzTeacher;
/**
 * 操作教师类
 * @author Jason
 *
 */
@SuppressWarnings("unchecked")
public class XzTeacherDao extends BaseDao{
	
	private Connection conn = null;
	private PreparedStatement pstmt = null;
	private ResultSet rs = null;
	
	/**
	 * 添加老师
	 * @param xt
	 * @return
	 */
	public int addTea(XzTeacher xt){
		StringBuffer sql=new StringBuffer("insert into xz_teacher values (SQ_XZTEACHER_ID.nextval,?,?,?,?,?,?,?,?,?)");
		String [] params=new String[] {xt.getTeaName(),xt.getTeaEnName(),xt.getTeaSex(),xt.getTeaLevel(),xt.getTeaMobile(),
				xt.getTeaSubId()+"",xt.getTeaSchId()+"",xt.getTeaStatus()+"",xt.getTeaDesc()};
		return this.executeSQL(sql.toString(), params);
	}
	/**
	 * 按校区ID查询老师
	 */
	public List searchTea(int schid){
		List list =new ArrayList();
		StringBuffer sql=new StringBuffer("select * from xz_teacher x where x.tea_sch_id = '"+schid+"' order by x.tea_name");
		try{
			conn=this.getConn();
			pstmt=conn.prepareStatement(sql.toString());
			rs=pstmt.executeQuery();
			while(rs.next()){
				XzTeacher xt=new XzTeacher();
				xt.setTeaId(rs.getInt("tea_id"));
				xt.setTeaName(rs.getString("tea_name"));
				xt.setTeaEnName(rs.getString("tea_enname"));
				xt.setTeaSex(rs.getString("tea_sex"));
				xt.setTeaLevel(rs.getString("tea_level"));
				xt.setTeaMobile(rs.getString("tea_mobile"));
				xt.setTeaSubId(rs.getInt("tea_sub_id"));
				xt.setTeaStatus(rs.getInt("tea_status"));
				xt.setTeaDesc(rs.getString("tea_desc"));
				list.add(xt);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			this.closeAll(conn, pstmt, rs);
		}
		return list;
	}
	
	/**
	 * 显示所有老师
	 * @param schId
	 * @return
	 */
	public List searchTeaAll(){
		List list =new ArrayList();
		StringBuffer sql=new StringBuffer("select * from xz_teacher t order by t.tea_name");
		try{
			conn=this.getConn();
			pstmt=conn.prepareStatement(sql.toString());
			rs=pstmt.executeQuery();
			while(rs.next()){
				XzTeacher xsAll=new XzTeacher();
				xsAll.setTeaId(rs.getInt("tea_id"));
				xsAll.setTeaName(rs.getString("tea_name"));
				xsAll.setTeaEnName(rs.getString("tea_enname"));
				xsAll.setTeaSex(rs.getString("tea_sex"));
				list.add(xsAll);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			this.closeAll(conn, pstmt, rs);
		}
		return list;
	}
	/**
	 * 按校区、年级、学科、教师姓名联合查询
	 * @return
	 */ 
	public List searchTeacherById(int schId,int graId,int subId){
		List list =new ArrayList();
		StringBuffer sqlgroup=new StringBuffer("group by t.tea_name ,s.sch_name,g.gra_name,su.sub_name " +
		"having sum(xz_basenum) >0 " +
		"order by sum(xz_winter)/sum(xz_basenum) desc");
		
		StringBuffer sql=new StringBuffer("select t.tea_name tn,s.sch_name sn,g.gra_name gn,su.sub_name sa,sum(xz_basenum) bas,sum(xz_winter) win,sum(xz_winbase) wb,sum(xz_spring) spr " +
				"from xuzhaonew xz ,xz_teacher t,xz_school s,xz_grade g,xz_subject su " +
				"where xz.xz_tea_id=t.tea_id " +
				"and xz.xz_sch_id=s.sch_id " +
				"and xz.xz_gra_id=g.gra_id " +
				"and xz.xz_sub_id=su.sub_id ");
				if(schId==0&&graId!=0&&subId!=0){
					sql.append("and g.gra_id=" + graId + " "+
			        "and su.sub_id=" + subId + " ").append(sqlgroup);
				}else if(graId==0&&schId!=0&&subId!=0){
					sql.append("and s.sch_id=" + schId + " "+
					"and su.sub_id=" + subId + " ").append(sqlgroup);
				}else if(subId==0&&schId!=0&&graId!=0){
					sql.append("and s.sch_id=" + schId + " "+
					"and g.gra_id=" + graId + " ").append(sqlgroup);
				}else if(schId==0&&graId==0&&subId!=0){
					sql.append("and su.sub_id=" + subId + " ").append(sqlgroup);
				}else if(schId==0&&subId==0&&graId!=0){
					sql.append("and g.gra_id=" + graId + " ").append(sqlgroup);
				}else if(schId!=0&&subId==0&&graId==0){
					sql.append("and s.sch_id=" + schId + " ").append(sqlgroup);
				}else if(schId!=0&&subId!=0&&graId!=0){
					sql.append("and s.sch_id=" + schId + " "+
			        "and g.gra_id=" + graId + " "+
			        "and su.sub_id=" + subId + " ").append(sqlgroup);
				}else if(schId==0&&graId==0&&subId==0){
					sql.append(sqlgroup);
				}
		try{
			conn=this.getConn();
			pstmt=conn.prepareStatement(sql.toString());
			rs=pstmt.executeQuery();
			while(rs.next()){
				XzTeaComplex xtc=new XzTeaComplex();
				xtc.setSchName(rs.getString("sn"));
				xtc.setGraName(rs.getString("gn"));
				xtc.setSubName(rs.getString("sa"));
				xtc.setTeaName(rs.getString("tn"));
				xtc.setBasSum(rs.getInt("bas"));
				xtc.setWinSum(rs.getInt("win"));
				xtc.setSprSum(rs.getInt("spr"));
				xtc.setWinBase(rs.getInt("wb"));
				list.add(xtc);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			this.closeAll(conn, pstmt, rs);
		}
		return list;
	}
	//Newadd
	public List showTeaNew(int schSeq,int graId,int subId){
		List list =new ArrayList();
		XzTeaComplex xtc=null;
		StringBuffer sqlgroup=new StringBuffer("group by t.tea_name ,s.sch_name,g.gra_name,su.sub_name " +
		"having sum(xz_basenum+xz_winbase) >0 " +
		"order by sum(xz_spring)/sum(xz_basenum+xz_winbase) desc");
		
		StringBuffer sql=new StringBuffer("select t.tea_name tn,s.sch_name sn,g.gra_name gn,su.sub_name sa," +
				"sum(xz_basenum) bas,sum(xz.xz_winbase) wb," +
				"sum(xz_winter) win,sum(xz_spring) spr " +
				"from xuzhaonew xz ,xz_teacher t,xz_school s,xz_grade g,xz_subject su " +
				"where xz.xz_tea_id=t.tea_id " +
				"and xz.xz_sch_id=s.sch_id " +
				"and xz.xz_gra_id=g.gra_id " +
				"and xz.xz_sub_id=su.sub_id ");
				if(schSeq==0&&graId!=0&&subId!=0){
					sql.append("and g.gra_id=" + graId + " "+
			        "and su.sub_id=" + subId + " ").append(sqlgroup);
				}else if(graId==0&&schSeq!=0&&subId!=0){
					sql.append("and s.sch_seq=" + schSeq + " "+
					"and su.sub_id=" + subId + " ").append(sqlgroup);
				}else if(subId==0&&schSeq!=0&&graId!=0){
					sql.append("and s.sch_seq=" + schSeq + " "+
					"and g.gra_id=" + graId + " ").append(sqlgroup);
				}else if(schSeq==0&&graId==0&&subId!=0){
					sql.append("and su.sub_id=" + subId + " ").append(sqlgroup);
				}else if(schSeq==0&&subId==0&&graId!=0){
					sql.append("and g.gra_id=" + graId + " ").append(sqlgroup);
				}else if(schSeq!=0&&subId==0&&graId==0){
					sql.append("and s.sch_seq=" + schSeq + " ").append(sqlgroup);
				}else if(schSeq!=0&&subId!=0&&graId!=0){
					sql.append("and s.sch_seq=" + schSeq + " "+
			        "and g.gra_id=" + graId + " "+
			        "and su.sub_id=" + subId + " ").append(sqlgroup);
				}else if(schSeq==0&&graId==0&&subId==0){
					sql.append(sqlgroup);
				}
		try{
			System.out.println(sql.toString());
			conn=this.getConn();
			pstmt=conn.prepareStatement(sql.toString());
			rs=pstmt.executeQuery();
			while(rs.next()){
				xtc=new XzTeaComplex();
				xtc.setSchName(rs.getString("sn"));
				xtc.setGraName(rs.getString("gn"));
				xtc.setSubName(rs.getString("sa"));
				xtc.setTeaName(rs.getString("tn"));
				xtc.setBasSum(rs.getInt("bas"));
				xtc.setSprSum(rs.getInt("spr"));
				xtc.setWinBase(rs.getInt("wb"));
				xtc.setWinSum(rs.getInt("win"));
				list.add(xtc);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			this.closeAll(conn, pstmt, rs);
		}
		return list;
	}
//	public static void main(String[] args) {
//		XzTeacherDao xt=new XzTeacherDao();
//		List li=xt.showTeaNew(0, 0, 0);
//		for(int i=0;i<li.size();i++){
//			XzTeaComplex x=(XzTeaComplex) li.get(i);
//			System.out.println(x.getWinBase()+" "+x.getWinSum());
//		}
//	}
	/**
	 * 按教师姓名查询
	 * @return
	 */ 
	//and t.tea_name like '%"+teaName+"%' 
	public List searchByteaName(String teaName){
		List list =new ArrayList();
		StringBuffer sql=new StringBuffer("select t.tea_name tn,s.sch_name sn,g.gra_name gn,su.sub_name sa,sum(xz_basenum) bas,sum(xz_winter) win,sum(xz_winbase) wb,sum(xz_spring) spr " +
				"from xuzhaonew xz ,xz_teacher t,xz_school s,xz_grade g,xz_subject su " +
				"where xz.xz_tea_id = t.tea_id " +
				"and xz.xz_sch_id = s.sch_id " +
				"and xz.xz_gra_id = g.gra_id " +
				"and xz.xz_sub_id = su.sub_id " +
				"and t.tea_name like '%"+teaName+"%' " +
				"group by t.tea_name ,s.sch_name,g.gra_name,su.sub_name " +
				"order by sum(xz_winter)/sum(xz_basenum) desc");
		try{
			conn=this.getConn();
			pstmt=conn.prepareStatement(sql.toString());
			rs=pstmt.executeQuery();
			while(rs.next()){
				XzTeaComplex xtc=new XzTeaComplex();
				xtc.setSchName(rs.getString("sn"));
				xtc.setGraName(rs.getString("gn"));
				xtc.setSubName(rs.getString("sa"));
				xtc.setTeaName(rs.getString("tn"));
				xtc.setBasSum(rs.getInt("bas"));
				xtc.setWinSum(rs.getInt("win"));
				xtc.setSprSum(rs.getInt("spr"));
				xtc.setWinBase(rs.getInt("wb"));
				list.add(xtc);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			this.closeAll(conn, pstmt, rs);
		}
		return list;
	}
}
